/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package web.Dao.impl;

import java.sql.Connection;
import web.util.DBUitl;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import web.enity.Book;
import web.Dao.BookDAO;

/**
 *
 * @author 10592
 */
public class BookDAOImpl  implements BookDAO{

    public BookDAOImpl() {
        super();
    }

    @Override
    public List<Book> selectPage(int pagenum, int rowcount) {
//        throw new UnsupportedOperationException("Not supported yet."); //To change body of generated methods, choose Tools | Templates.
            List<Book> books=new ArrayList<>();
            PreparedStatement ps=null;
            ResultSet rs=null;
            try(Connection con = DBUitl.getConnection()){
            ps = con.prepareStatement("select * from book LIMIT ?,?");
            ps.setInt(1, (pagenum - 1) * rowcount);
            ps.setInt(2, rowcount);
            rs = ps.executeQuery();
            while (rs.next()) {
                Book b = ecapsulate(rs);
                books.add(b);
            }
            } catch (Exception e) {
                e.printStackTrace();
            }
            return books;
    }

    @Override
    public int selectCount() {
//        throw new UnsupportedOperationException("Not supported yet."); //To change body of generated methods, choose Tools | Templates.
           int rows = 0;
           
           PreparedStatement ps=null;
           ResultSet rs=null;
        try(Connection con = DBUitl.getConnection()) {
            String sqlString = "select count(*) from book";
            ps = con.prepareStatement(sqlString);
            rs = ps.executeQuery();
            if (rs.next()) {
                rows = rs.getInt(1);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return rows;
        
    }

    @Override
    public int insert(Book book) {
//        throw new UnsupportedOperationException("Not supported yet."); //To change body of generated methods, choose Tools | Templates.
        int result = 0;      
        PreparedStatement ps=null;
        try (Connection con = DBUitl.getConnection()){
            ps = con.prepareStatement("INSERT INTO book(bookName,images,stock,price) VALUES(?,?,?,?)");
            ps.setString(1, book.getBookName());
            ps.setString(2, book.getImage());
            ps.setInt(3, book.getStock());
            ps.setDouble(4, book.getPrice());
            result = ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return  result;
    }
    @Override
    public Book select(int bid) {
//        throw new UnsupportedOperationException("Not supported yet."); //To change body of generated methods, choose Tools | Templates.
            PreparedStatement ps=null;
            ResultSet  rs=null;
        try (Connection con = DBUitl.getConnection()){
            ps = con.prepareStatement("SELECT * from book where bid=? ");
            ps.setInt(1, bid);
            rs = ps.executeQuery();
            if (rs.next()) {
                return ecapsulate(rs);
            }
        }   catch (SQLException ex) {
            Logger.getLogger(BookDAOImpl.class.getName()).log(Level.SEVERE, null, ex);
        }
            return null;
        }

    @Override
    public int update(Book book) {
//        throw new UnsupportedOperationException("Not supported yet."); //To change body of generated methods, choose Tools | Templates.
          int result = 0;
          PreparedStatement  ps=null;
        try (Connection con = DBUitl.getConnection()){
            ps = con.prepareStatement("UPDATE book SET bookName=?,price=?,images=?,stock=? where bid=?");
            ps.setString(1, book.getBookName());
            ps.setDouble(2, book.getPrice());
            ps.setString(3, book.getImage());
            ps.setInt(4, book.getStock());
            ps.setInt(5, book.getBid());
            result = ps.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(BookDAOImpl.class.getName()).log(Level.SEVERE, null, ex);
        }
             return result;
        
    }

    private Book ecapsulate(ResultSet rs) throws SQLException {
//        throw new UnsupportedOperationException("Not supported yet."); //To change body of generated methods, choose Tools | Templates.
        Book b=new Book();
        b.setBid(rs.getInt("bid"));
        b.setBookName(rs.getString("bookName"));
        b.setImage(rs.getString("images"));
        b.setPrice(rs.getDouble("price"));
        b.setStock(rs.getInt("stock"));
        return b;
    }
    public static void main(String[] args) {
        BookDAO dao = new BookDAOImpl();      
        List<Book> bl = dao.selectPage(2, 10);
        System.out.println("共查询出 " + bl.size() + " 条数据");
        System.out.println("ID\t商品名\t商品价格\t库存");
        for (Book book : bl) {
            System.out.println(book.getBookName() + "\t" + book.getPrice() + "\t" + book.getStock());
        }
    }
}
